const xlsx = require("xlsx");
const db = require("../db/connMysql");
const util = require("util");
const readline = require("readline");

const query = util.promisify(db.query).bind(db);

// 创建命令行交互接口
const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout,
});

// 硬编码学院数据
const FACULTIES = [
  { facultyId: 8, name: "健康医疗科技学院" },
  { facultyId: 5, name: "外国语学院" },
  { facultyId: 2, name: "信息管理与工程学院" },
  { facultyId: 3, name: "商务管理学院" },
  { facultyId: 9, name: "其他" },
  { facultyId: 6, name: "国际教育学院" },
  { facultyId: 7, name: "继续教育学院" },
  { facultyId: 1, name: "计算机学院" },
  { facultyId: 4, name: "数字媒体与设计学院" },
];

// 添加硬编码年级数据
const GRADES = [
  { gradeId: 1, name: "2021级", year: 2021 },
  { gradeId: 2, name: "2022级", year: 2022 },
  { gradeId: 3, name: "2023级", year: 2023 },
  { gradeId: 4, name: "2024级", year: 2024 },
  { gradeId: 5, name: "2025级", year: 2025 },
];

// 封装询问函数
const askQuestion = (question) => {
  return new Promise((resolve) => {
    rl.question(question, (answer) => {
      resolve(answer.toLowerCase());
    });
  });
};

async function initializeTeachers() {
  // 读取教师Excel文件
  const teacherWorkbook = xlsx.readFile("./docs/excel/teachers.xlsx");
  const teacherSheet = teacherWorkbook.Sheets[teacherWorkbook.SheetNames[0]];
  const teachers = xlsx.utils.sheet_to_json(teacherSheet);

  // 打印读取到的教师数据
  console.log("\n读取到的教师数据：");
  console.table(teachers);

  const answer = await askQuestion("\n是否确认插入以上教师数据？(y/n): ");
  if (answer !== "y") {
    console.log("已取消教师数据插入");
    return;
  }

  // 使用硬编码的学院数据建立映射
  const facultyMap = new Map(FACULTIES.map((f) => [f.name, f.facultyId]));

  // 插入教师数据
  for (const teacher of teachers) {
    const facultyId = facultyMap.get(teacher["学院"]);
    if (!facultyId) {
      console.warn(`警告：找不到学院 "${teacher["学院"]}" 的ID`);
      continue;
    }

    await query(
      `INSERT INTO Users 
            (accountName, username, workId, facultyId, password, phone, userTypeId, email) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        teacher["素质教师姓名"],
        teacher["素质教师姓名"],
        teacher["工号"] || "",
        facultyId,
        "123456",
        teacher["联系方式"],
        4,
        teacher["邮箱"] || "",
      ]
    );
  }
  console.log("教师数据初始化完成！");
}

async function initializeClasses() {
  // 读取班级Excel文件
  const classWorkbook = xlsx.readFile("./docs/excel/classes.xlsx");
  const classSheet = classWorkbook.Sheets[classWorkbook.SheetNames[0]];
  const classes = xlsx.utils.sheet_to_json(classSheet);

  // 打印读取到的班级数据
  console.log("\n读取到的班级数据：");
  console.table(classes);

  const answer = await askQuestion("\n是否确认插入以上班级数据？(y/n): ");
  if (answer !== "y") {
    console.log("已取消班级数据插入");
    return;
  }

  // 使用硬编码的学院数据建立映射
  const facultyMap = new Map(FACULTIES.map((f) => [f.name, f.facultyId]));

  // 使用硬编码的年级数据建立映射
  const gradeMap = new Map(GRADES.map((g) => [g.name, g.gradeId]));

  // 首先获取用户ID
  const users = await query(
    "SELECT uid, username FROM Users WHERE userTypeId = 4"
  );
  const userMap = new Map(users.map((t) => [t.username, t.uid]));

  // 然后获取教师ID
  const teachers = await query("SELECT teacherId, uid FROM Teachers");
  const teacherMap = new Map(teachers.map((t) => [t.uid, t.teacherId]));

  // 定义有效的层次值映射
  const validLevels = {
    本科: "本科",
    专升本: "专升本",
    专科: "专科",
  };

  // 插入班级数据
  for (const cls of classes) {
    const facultyId = facultyMap.get(cls["学院"]);
    const userId = userMap.get(cls["素质教师姓名"]);
    const teacherId = userId ? teacherMap.get(userId) : null;
    const gradeId = gradeMap.get(cls["年级"]);

    if (!facultyId) {
      console.warn(`警告：找不到学院 "${cls["学院"]}" 的ID`);
      continue;
    }
    if (!userId) {
      console.warn(`警告：找不到用户 "${cls["素质教师姓名"]}" 的ID`);
      continue;
    }
    if (!teacherId) {
      console.warn(`警告：找不到教师ID，用户ID: ${userId}`);
      continue;
    }
    if (!gradeId) {
      console.warn(`警告：找不到年级 "${cls["年级"]}" 的ID`);
      continue;
    }

    // 验证层次值是否有效
    const level = validLevels[cls["层次"]];
    if (!level) {
      console.warn(
        `警告：无效的层次值 "${cls["层次"]}"，必须是 "本科"、"专升本" 或 "专科" 之一`
      );
      continue;
    }

    try {
      await query(
        `INSERT INTO Classes 
                (facultyId, teacherId, gradeId, name, major, level, isDel) 
                VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          facultyId,
          teacherId,
          gradeId,
          cls["班级名称"],
          cls["专业名称"],
          level,
          "false",
        ]
      );
    } catch (error) {
      console.error(`插入班级数据失败：${cls["班级名称"]}`, error);
      console.error("数据详情：", {
        facultyId,
        teacherId,
        gradeId,
        name: cls["班级名称"],
        major: cls["专业名称"],
        level: level,
      });
      continue;
    }
  }
  console.log("班级数据初始化完成！");
}

async function initializeData() {
  try {
    await initializeTeachers();
    await initializeClasses();
    console.log("所有数据初始化完成！");
    rl.close();
    process.exit(0);
  } catch (error) {
    console.error("初始化数据时出错：", error);
    rl.close();
    process.exit(1);
  }
}

// 等待数据库连接成功后再执行初始化
const checkConnection = setInterval(() => {
  if (db.conn_successful) {
    clearInterval(checkConnection);
    initializeData();
  }
}, 100);
